﻿#include "dbhelper.h"
#include <QSqlDatabase>
#include <QMessageBox>
#include <QSqlError>
#include <QSqlQuery>
#include <QDebug>

DbHelper::DbHelper(QObject *parent) : QObject(parent)
{
    createProductTable();
    createUserRelationTable();
    createFormulaTable();
}

DbHelper::~DbHelper()
{
    db.close();
}

bool DbHelper::createConnection(const QString database)
{
    if(QSqlDatabase::contains("my_conn"))
    {
        db = QSqlDatabase::database("my_conn");
    }else{
        db = QSqlDatabase::addDatabase("QSQLITE", "my_conn");
    }

    db.setDatabaseName(database);

    if(db.open()){
        return true;
    }else{
        qDebug()<<"数据库打开失败!";
        QMessageBox::critical(nullptr, "错误", db.lastError().text());
        return false;
    }
}

bool DbHelper::isTableExist(QString tableName)
{
    if(!db.isOpen()){
        createConnection(database);
    }

    QSqlQuery query(db);
    QString sql = QString("select * from sqlite_master where name='%1'").arg(tableName);
    query.exec(sql);
    return query.next();
}

void DbHelper::createProductTable(){
    if(!db.isOpen()){
        createConnection(database);
    }

    if(!isTableExist("product")){
        qDebug()<<"create table product...";
        QSqlQuery query(db);
        QString sql = "CREATE TABLE product ("
                      "id int(11),"
                      "product_id varchar(255),"
                      "company_id varchar(255),"
                      "type varchar(255),"
                      "type_pinyin varchar(255),"
                      "name varchar(255),"
                      "name_pinyin varchar(255),"
                      "unit varchar(255),"
                      "upper_limit int(11),"
                      "lower_limit int(11),"
                      "product_num varchar(255),"
                      "count int(11),"
                      "sanbao int(11),"
                      "in_price double,"
                      "specifications varchar(255),"
                      "brand varchar(255),"
                      "origin_place varchar(255),"
                      "remark varchar(255),"
                      "bar_code varchar(255),"
                      "guide_price1 double,"
                      "guide_price2 double,"
                      "primary_directory varchar(255),"
                      "secondary_directory varchar(255))";
        query.exec(sql);
    }
}

void DbHelper::insertProduct(Product &product)
{
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = QString("insert into product values("
                          "'%1','%2','%3','%4','%5','%6',"
                          "'%7','%8',%9,%10,'%11',%12,"
                          "%13,%14,'%15','%16','%17','%18',"
                          "'%19',%20,%21,'%22','%23')")
            .arg(product.id)
            .arg(product.productId)
            .arg(product.companyId)
            .arg(product.type)
            .arg(product.typePinyin)
            .arg(product.name)
            .arg(product.namePinyin)
            .arg(product.unit)
            .arg(product.upperLimit)
            .arg(product.lowerLimit)
            .arg(product.productNum)
            .arg(product.count)
            .arg(product.sanbao)
            .arg(product.inPrice)
            .arg(product.specifications)
            .arg(product.brand)
            .arg(product.originPlace)
            .arg(product.remark)
            .arg(product.barCode)
            .arg(product.guidePrice1)
            .arg(product.guidePrice2)
            .arg(product.primaryDirectory)
            .arg(product.secondaryDirectory);

    QSqlQuery query(db);
    query.exec(sql);
}

void DbHelper::updateProduct(Product *product){
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = QString("update product set "
                          "type='%1', "
                          "type_pinyin='%2', "
                          "name='%3', "
                          "name_pinyin='%4', "
                          "unit='%5', "
                          "upper_limit=%6, "
                          "lower_limit=%7, "
                          "product_num='%8', "
                          "count=%9, "
                          "sanbao=%10, "
                          "in_price=%10, "
                          "specifications='%11',"
                          "brand='%12', "
                          "origin_place='%13', "
                          "remark='%14', "
                          "bar_code='%15', "
                          "guide_price1=%16, "
                          "guide_price2=%17, "
                          "primary_directory='%18', "
                          "secondary_directory='%19' "
                          "where product_id='%20'")
            .arg(product->type).arg(product->typePinyin).arg(product->name).arg(product->namePinyin)
            .arg(product->unit).arg(product->upperLimit).arg(product->lowerLimit).arg(product->count)
            .arg(product->sanbao).arg(product->inPrice).arg(product->specifications).arg(product->brand)
            .arg(product->originPlace).arg(product->remark).arg(product->barCode).arg(product->guidePrice1)
            .arg(product->guidePrice2).arg(product->primaryDirectory).arg(product->secondaryDirectory).arg(product->productId);

    QSqlQuery query(db);
    query.exec(sql);
}

void DbHelper::clearProduct()
{
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql="delete from product";

    QSqlQuery query(db);
    query.exec(sql);
}

Product* DbHelper::getProductById(QString productId)
{
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = QString("select * from product where product_id = %1").arg(productId);

    QSqlQuery query(db);
    query.exec(sql);

    Product *product = new Product();
    if(query.next()){
        product->loadFromSqlQuery(query);
        return product;
    }else{
        return nullptr;
    }
}

QList<Product*> DbHelper::getAllProducts()
{
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = "select * from product";

    QSqlQuery query(db);
    query.exec(sql);

    QList<Product*> products;

    while (query.next()) {
        Product *product = new Product();
        product->loadFromSqlQuery(query);
        products.append(product);
    }

    return products;
}

QList<Product*> DbHelper::queryProducts(
        QString overallQuery,
        QString typeQuery,
        QString nameQuery,
        QString primaryQuery,
        QString secondaryQuery)
{
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = "select * from product where "
                  "(name like '%"+nameQuery+"%' or name_pinyin like '%"+nameQuery+"%') and"
                  "(type like '%"+typeQuery+"%' or type_pinyin like '%"+typeQuery+"%') and"
                  "(name like '%"+overallQuery+"%' or name_pinyin like '%"+overallQuery+"%' or "
                   "type like '%"+overallQuery+"%' or type_pinyin like '%"+overallQuery+"%') and "
                   "primary_directory like '%"+primaryQuery+"%' and "
                   "secondary_directory like '%"+secondaryQuery+"%'";

    qDebug()<<sql.toUtf8().data();

    QSqlQuery query(db);
    query.exec(sql);

    QList<Product*> products;

    while (query.next()) {
        Product *product = new Product();
        product->loadFromSqlQuery(query);
        products.append(product);
    }

    return products;
}

QList<Product*> DbHelper::queryPlanProducts(){
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = "select * from product where count < lower_limit";

    QSqlQuery query(db);
    query.exec(sql);

    QList<Product*> products;

    while (query.next()) {
        Product *product = new Product();
        product->loadFromSqlQuery(query);
        products.append(product);
    }

    return products;
}

QList<Product*> DbHelper::getSanbaoProducts(){
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = "select * from product where sanbao > 0";

    QSqlQuery query(db);
    query.exec(sql);

    QList<Product*> products;

    while (query.next()) {
        Product *product = new Product();
        product->loadFromSqlQuery(query);
        products.append(product);
    }

    return products;
}

void DbHelper::createUserRelationTable()
{
    if(!db.isOpen()){
        createConnection(database);
    }

    if(!isTableExist("user_relation")){
        qDebug()<<"create table user_relation...";
        QSqlQuery query(db);
        QString sql = "CREATE TABLE user_relation ("
                      "id int(11),"
                      "source_username varchar(255),"
                      "source_company_id varchar(255),"
                      "target_username varchar(255),"
                      "target_company_id varchar(255),"
                      "nick_name varchar(255),"
                      "nick_name_pinyin varchar(255),"
                      "contact varchar(255),"
                      "contact_pinyin varchar(255),"
                      "tel varchar(255),"
                      "type int(11),"
                      "is_platform int(11),"
                      "ticket_in double,"
                      "ticket_out double,"
                      "money_in double,"
                      "money_out double,"
                      "pre_ticket_in double,"
                      "pre_ticket_out double,"
                      "pre_money_in double,"
                      "pre_money_out double,"
                      "has_sub_account int(11),"
                      "has_ticket_history int(11),"
                      "credit int(11),"
                      "remark varchar(255),"
                      "primary_directory varchar(255),"
                      "secondary_directory varchar(255))";

        query.exec(sql);
    }
}

void DbHelper::insertUserRelation(UserRelation &relation)
{
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = QString("insert into user_relation values("
                          "'%1','%2','%3','%4','%5','%6',"
                          "'%7','%8','%9','%10','%11','%12',"
                          "'%13','%14','%15','%16','%17','%18',"
                          "'%19',%20,%21,'%22','%23','%24','%25','%26')")
            .arg(relation.id)
            .arg(relation.sourceUsername)
            .arg(relation.sourceCompanyId)
            .arg(relation.targetUsername)
            .arg(relation.targetCompanyId)
            .arg(relation.nickName)
            .arg(relation.nickNamePinyin)
            .arg(relation.contact)
            .arg(relation.contactPinyin)
            .arg(relation.tel)
            .arg(relation.type)
            .arg(relation.isPlatform)
            .arg(relation.ticketIn)
            .arg(relation.ticketOut)
            .arg(relation.moneyIn)
            .arg(relation.moneyOut)
            .arg(relation.preTicketIn)
            .arg(relation.preTicketOut)
            .arg(relation.preMoneyIn)
            .arg(relation.preMoneyOut)
            .arg(relation.hasSubAccount)
            .arg(relation.hasTicketHistory)
            .arg(relation.credit)
            .arg(relation.remark)
            .arg(relation.primaryDirectory)
            .arg(relation.secondaryDirectory);

    QSqlQuery query(db);
    query.exec(sql);
}

void DbHelper::clearUserRelation()
{
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql="delete from user_relation";

    QSqlQuery query(db);
    query.exec(sql);
}

void DbHelper::clearUserRelationByType(UserRelation::RelationType type)
{
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = QString("delete from user_relation where type = %1 or type = %2").arg(type).arg(type+2);

    QSqlQuery query(db);
    query.exec(sql);
}

UserRelation* DbHelper::getUserRelation(QString sourceUsername, QString targetUsername, UserRelation::RelationType type){
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = QString("select * from user_relation where source_username = '%1' and target_username = '%2' and (type = %3 or type = %4)")
            .arg(sourceUsername).arg(targetUsername).arg(type).arg(3);

    QSqlQuery query(db);
    query.exec(sql);

    if(query.next()){
        UserRelation *relation = new UserRelation();
        relation->loadFromSqlQuery(query);
        return relation;
    }else{
        return nullptr;
    }
}

QList<UserRelation*> DbHelper::getAllUserRelationsByType(UserRelation::RelationType type)
{
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = QString("select * from user_relation where type = %1 or type = %2").arg(type).arg(3);

    QSqlQuery query(db);
    query.exec(sql);

    QList<UserRelation*> products;

    while (query.next()) {
        UserRelation *product = new UserRelation();
        product->loadFromSqlQuery(query);
        products.append(product);
    }

    return products;
}

QList<UserRelation*> DbHelper::queryUserRelation(
        UserRelation::RelationType type,
        QString overallQuery,
        QString nickNameQuery,
        QString contactQuery,
        QString telQuery,
        QString primaryQuery,
        QString secondaryQuery)
{
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = "select * from user_relation where "
                  "(nick_name like '%"+nickNameQuery+"%' or nick_name_pinyin like '%"+nickNameQuery+"%') and"
                  "(contact like '%"+contactQuery+"%' or contact_pinyin like '%"+contactQuery+"%') and"
                  "(nick_name like '%"+overallQuery+"%' or nick_name_pinyin like '%"+overallQuery+"%' or "
                   "contact like '%"+overallQuery+"%' or contact_pinyin like '%"+overallQuery+"%') and "
                   "tel like '%"+telQuery+"%' and "
                   "primary_directory like '%"+primaryQuery+"%' and "
                   "secondary_directory like '%"+secondaryQuery+"%' and "
                   "(type = "+QString("%1").arg(type)+" or "
                   "type = "+QString("%1").arg(3)+")";

    qDebug()<<sql.toUtf8().data();

    QSqlQuery query(db);
    query.exec(sql);

    QList<UserRelation*> relations;

    while (query.next()) {
        UserRelation *relation = new UserRelation();
        relation->loadFromSqlQuery(query);
        relations.append(relation);
    }

    return relations;
}

void DbHelper::createFormulaTable()
{
    if(!db.isOpen()){
        createConnection(database);
    }

    if(!isTableExist("formula")){
        qDebug()<<"create table formula...";
        QSqlQuery query(db);
        QString sql = "CREATE TABLE formula ("
                      "id int(11),"
                      "company_id varchar(255),"
                      "combine_id varchar(255),"
                      "combine_count int(11),"
                      "split_ids varchar(255),"
                      "split_counts varchar(255))";
        query.exec(sql);
    }
}

void DbHelper::insertFormula(FormulaBase &formulaBase)
{
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = QString("insert into formula values("
                          "'%1','%2','%3','%4','%5','%6')")
            .arg(formulaBase.id)
            .arg(formulaBase.companyId)
            .arg(formulaBase.combineId)
            .arg(formulaBase.combineCount)
            .arg(formulaBase.splitIds)
            .arg(formulaBase.splitCounts);

    QSqlQuery query(db);
    query.exec(sql);
}

void DbHelper::clearFormula()
{
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = "delete from formula";

    QSqlQuery query(db);
    query.exec(sql);
}

QList<FormulaBase*> DbHelper::getAllFormulas()
{
    if(!db.isOpen()){
        createConnection(database);
    }

    QString sql = "select * from formula";

    QSqlQuery query(db);
    query.exec(sql);

    QList<FormulaBase*> formulsBases;

    while (query.next()) {
        FormulaBase *formulaBase = new FormulaBase();
        formulaBase->loadFromSqlQuery(query);
        formulsBases.append(formulaBase);
    }

    return formulsBases;
}
